<!--#include file="lib/utf-8.asp"-->
<!--#include file="lib/connopen.asp"-->
<!--#include file="lib/asp-common-lib.asp"-->
<!--#include file="lib/upload_utf_8.asp"-->
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CC的办公系统</title>
<!--#include file="lib/plugins.asp"-->
<style type="text/css">
	.ui-datepicker table {
		border: 0;
	}
</style>
<script type="text/javascript">
	$(function() {
		$("#xls_form").validationEngine();
	});
</script>
<!--#include file="lib/js-google-analytics.asp"-->
</head>

<body>
<!--#include file="lib/header.asp"-->
<!--#include file="lib/nav.asp"-->
<div id="main">
<!--#include file="lib/col1.asp"-->
<!--#include file="lib/col2.asp"-->
	<div id="col3" role="main">
		<div id="col3_content" class="clearfix">
<%
if not CheckPostFlag(session("aaPostFlag"),2,15) then call JumpErrorPage(2)
dim upload,file,formName,formPath,iCount,up_xls,xls_xml,xls_title,thistime,xls_name,xls_id,rows,sql_tele,rs_tele,rs_find,empe_index,sql_report
set upload=new upload_5xsoft
if upload.form("submit")<>"导入" then
%>
<form id="xls_form" name="xls_form" method="post" action="#" enctype="multipart/form-data">
	<input id="filepath" name="filepath" style="display:none" value="xls" />

	<table class="full">
		<tr>
			<th>上传Excel表格</th>
		</tr>
		<tr>
			<td>账单名称:<%if month(date())=1 then response.write(year(date())-1) else response.write(year(date()))%>年<input id="xls_title" name="xls_title" class="validate[required,custom[onlyNumber],minSize[1],maxSize[2],min[1],max[12]]" maxlength="2" size="2" value="<%=month(DateAdd("m",-1,date()))%>" />月电话费</td>
		</tr>
		<tr>
			<td id="upid">选择文件:<input name="xls_file" type="file" size="60" /></td>
		</tr>
		<tr>
			<td>上传前务必确认电话费分摊规则，并检查权重情况(注意：2012年9月份开始账单格式有变化，不能上传9月份以前的账单)</td>
		</tr>
		<tr>
			<td><input type="submit" name="submit" value="导入" /></td>
		</tr>
	</table>
</form>

<%
else
	conn.BeginTrans
	' ON ERROR RESUME NEXT
	up_xls=false
	xls_xml=false
	formPath=upload.form("filepath")
	xls_title=upload.form("xls_title")
	'xls_name=upload.form("xls_name")
	if right(formPath,1)<>"/" then formPath=formPath&"/" 
	'response.write(xls_file)
	'response.end
	'if right(xls_file,3)<>"xls" then
	'	response.write("非Excel文件！")
	'else
		for each formName in upload.objFile 
			set file=upload.file(formName)
			if file.FileExt<>"xls" and file.FileExt<>"xlsx" then
				response.write("非Excel文件！")
				exit for
			end if
			if file.FileExt="xlsx" then xls_xml=true
			if file.FileSize>0 then
				thistime=now()
				' xls_name=formPath&"亿泰_"&year(thistime)&month(thistime)&day(thistime)&hour(thistime)&minute(thistime)&second(thistime)&int(10*rnd())&"."&file.FileExt
				Randomize
				xls_name=formPath&"亿泰_"&FormatTime(thistime,"{Y}-{m}-{d}_{h}-{n}-{s}")&"_"&int(10*rnd())&"."&file.FileExt
				up_xls=file.SaveAs(Server.mappath(xls_name))
				if up_xls then
					response.write "上传成功！"&file.FilePath&file.FileName&" ("&file.FileSize&") => <a href='"&xls_name&"'>"&xls_name&"</a><br />"
					xls_id=InsertXLS(xls_name,xls_title)
				end if
			end if
			set file=nothing
		next
	'end if
	'ON ERROR RESUME NEXT
	if up_xls then
		rows=AdoExcel(xls_name,xls_xml)
		' response.write(rows)
		if rows=0 then call DeleteFile(xls_name)
		'report
		sql_tele="select * from tele_all"
		set rs_tele=server.createobject("adodb.recordset")
		rs_tele.open sql_tele,conn,1,1
		set rs_find=server.createobject("adodb.recordset")
		report_fee=0
		do while not rs_tele.eof
			sql_find="select fee_hj from fee where fee_xls="&xls_id&" and fee_yhhm='"&rs_tele("tele_num")&"'"
			rs_find.open sql_find,conn,1,1
			if not rs_find.eof then 
				report_fee=rs_find("fee_hj")
			else
				report_fee=0
			end if
			rs_find.close
			if rs_tele("empe_index")="" or isnull(rs_tele("empe_index")) then empe_index=0 else empe_index=rs_tele("empe_index")
			sql_report="insert into report(report_xls,report_tele,report_empe,report_dept,report_index,report_empeindex,report_weight,report_fee) values("&xls_id&",'"&rs_tele("tele_num")&"','"&rs_tele("empe_name")&"','"&rs_tele("dept_name")&"',"&rs_tele("dept_index")&","&empe_index&","&rs_tele("tele_weight")&","&ccur(report_fee)&")"
			call ExecuteCommand(sql_report,false)
			rs_tele.movenext
		loop
		set rs_find=nothing
		rs_tele.close
		set rs_tele=nothing
		
		if err.number=0 then 
			conn.CommitTrans  '如果没有conn错误，则执行事务提交
		else
			conn.RollbackTrans '否则回滚
			'回滚后的其他操作
			
			'strerr=err.Description
			url=request.ServerVariables("http_host")&request.ServerVariables("url")
			Response.Write "数据库错误！<br /><font color=red>Error # " & CStr(Err.Number) & "<br />" & Err.Description & "<br />Source: " & Err.Source &"<br />错误页面："&url&"</font><br />"
			' 删除上传的文件
			if DeleteFile(xls_name) then response.write xls_name&" =>删除成功！<br />"
			Response.End
		end if
	end if
end if
set upload=nothing 


function InsertXLS(xls_name,xls_title)
	dim sql
	if month(date())=1 then xls_title=year(date())-1&"年"&xls_title&"月电话费" else xls_title=year(date())&"年"&xls_title&"月电话费"
	sql="insert into xls(xls_name,xls_date,xls_type,xls_title) values('"&xls_name&"','"&date()&"',1,'"&xls_title&"')"
	InsertXLS=ExecuteCommand(sql,true)
end function


function AdoExcel(xls_name,xls_xml)
		dim xlsconn,connStr,rs,rows,cells,p,j,sql,sql_fee
		response.write("开始导入"&xls_name&"...<br />")
		set xlsconn=Server.CreateObject("ADODB.Connection")
		connStr="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&Server.MapPath(xls_name)&";Extended Properties='Excel 12.0"
		if xls_xml then connStr=connStr&" Xml"
		connStr=connStr&";HDR=NO;IMEX=1';"
		xlsconn.open connStr
		set rs=server.createobject("ADODB.Recordset")
		sql="select * from [Sheet1$]"
		rs.open sql,xlsconn,1,1
		' rows=rs.recordcount
		rows=0
		cells=rs.Fields.Count
		' set objExcelApp = CreateObject("Excel.Application") 
		' objExcelApp.DisplayAlerts = false
		' objExcelApp.Application.Visible = false
		' objExcelApp.WorkBooks.Open(Server.MapPath(xls_name))
		' set objExcelBook = objExcelApp.ActiveWorkBook
		' set objExcelSheets = objExcelBook.Worksheets
		' set objExcelSheet = objExcelBook.Sheets(2)
		p=false 
		response.write("<table>")
		do while not rs.eof
		'for i=1 to rows
			if p and len(rs.fields(0).value)<>8 then
				response.write("</tbody>")
				exit do
			end if
			if rs.fields(0).value="用户号码" then
				response.write("<thead><tr>")
				for j=0 to cells-1
					'if objExcelSheet.cells(i,j).value<>"" then
						
						response.write("<th>"&rs.fields(j).value&"</th>")
					'end if
				next
				response.write("</tr></thead>")
			end if
			if p then	
				'response.write(i&" ")
				response.write("<tr>")
				for j=0 to cells-1
					'if objExcelSheet.cells(i,j).value<>"" then
						response.write("<td>"&rs.fields(j).value&"</td>")
					'end if
				next
				response.write("</tr>")
				fee_yhhm=trim(rs.fields(0).value)
				fee_yjbf=ccur(rs.fields(1).value)
				fee_yytxf=ccur(rs.fields(2).value)
				fee_dxcxf=ccur(rs.fields(3).value)
				fee_swjsjtxf=ccur(rs.fields(4).value)
				fee_zhxxfwf=ccur(rs.fields(5).value)
				fee_sjywf=ccur(rs.fields(6).value)
				fee_wyzyf=ccur(rs.fields(7).value)
				fee_ycxfy=ccur(rs.fields(8).value)
				fee_qtf=ccur(rs.fields(9).value)
				fee_yhfy=ccur(rs.fields(10).value)
				fee_cnxfbc=ccur(rs.fields(11).value)
				fee_dsf=ccur(rs.fields(12).value)
				fee_hj=ccur(rs.fields(13).value)
				sql_fee="insert into fee(fee_xls,fee_yhhm,fee_yjbf,fee_yytxf,fee_dxcxf,fee_swjsjtxf,fee_zhxxfwf,fee_sjywf,fee_wyzyf,fee_ycxfy,fee_qtf,fee_yhfy,fee_cnxfbc,fee_dsf,fee_hj)"&_
                " values("&xls_id&",'"&fee_yhhm&"',"&fee_yjbf&","&fee_yytxf&","&fee_dxcxf&","&fee_swjsjtxf&","&fee_zhxxfwf&","&fee_sjywf&","&fee_wyzyf&","&fee_ycxfy&","&fee_qtf&","&fee_yhfy&","&fee_cnxfbc&","&fee_dsf&","&fee_hj&")"
				' fee_zzywf=ccur(rs.fields(2).value)
				' fee_bdhf=ccur(rs.fields(3).value)
				' fee_xnwhf=ccur(rs.fields(4).value)
				' fee_chf=ccur(rs.fields(5).value)
				' fee_dxf=ccur(rs.fields(6).value)
				' fee_myf=ccur(rs.fields(7).value)
				' fee_hlwsyf=ccur(rs.fields(8).value)
				' fee_xxf=ccur(rs.fields(9).value)
				' fee_sjywf=ccur(rs.fields(10).value)
				' fee_wyzyf=ccur(rs.fields(11).value)
				' fee_yhf=ccur(rs.fields(12).value)
				' fee_dsf=ccur(rs.fields(13).value)
				' fee_ywslf=ccur(rs.fields(14).value)
				' fee_qtf=ccur(rs.fields(15).value)
				' fee_hj=ccur(rs.fields(16).value)
				' sql_fee="insert into fee(fee_xls,fee_yhhm,fee_yjbf,fee_zzywf,fee_bdhf,fee_xnwhf,fee_chf,fee_dxf,fee_myf,fee_hlwsyf,fee_xxf,fee_sjywf,fee_wyzyf,fee_yhf,fee_dsf,fee_ywslf,fee_qtf,fee_hj)"&_
                ' " values("&xls_id&",'"&fee_yhhm&"',"&fee_yjbf&","&fee_zzywf&","&fee_bdhf&","&fee_xnwhf&","&fee_chf&","&fee_dxf&","&fee_myf&","&fee_hlwsyf&","&fee_xxf&","&fee_sjywf&","&fee_wyzyf&","&fee_yhf&","&fee_dsf&","&fee_ywslf&","&fee_qtf&","&fee_hj&")"
				call ExecuteCommand(sql_fee,false)
				rows=rows+1
			end if
			if rs.fields(0).value="固话" then
				response.write("<tbody>")
				p=true
			end if
		'next
			rs.movenext
		loop
		response.write("</table>")
		rs.close
		set rs=nothing
		xlsconn.close
		set xlsconn=nothing
		AdoExcel=rows
end function
%>
		</div>
	</div>
</div>
<!--#include file="lib/footer.asp"-->
</body>
</html>
<!--#include file="lib/connclose.asp"-->

